﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace HolidayDataAccessLayer
{
    
    public class HolidayDataAccess
    {
        HolidayDataClassesDataContext context;
        
        public HolidayDataAccess()
        {
            context = new HolidayDataClassesDataContext();
        }

        public void CreateTour(string destination,string refNumber,decimal price,DateTime startDate,DateTime endDate,string boardName,string[] facilities,string[] transports)
        {
            Destination dest = new Destination();
            dest.DestinationName = destination;
            dest.ReferenceNumber = refNumber;
            dest.Price =  price;
            dest.StartDate = startDate;
            dest.EndDate = endDate;

            Board board = (from b in context.Boards
                          where b.BoardName == boardName
                          select b).SingleOrDefault();
            if (board == null)
            {
                board = new Board();
                board.BoardName = boardName;
                context.Boards.InsertOnSubmit(board);
                context.SubmitChanges();
            }
            
            dest.BoardId = board.BoardId;

            context.Destinations.InsertOnSubmit(dest);
            context.SubmitChanges();
            
            foreach(var transportName in transports)
            {
                TransportType tt = (from t in context.TransportTypes
                                   where t.TransportTypeName == transportName
                                   select t).SingleOrDefault();
                if (tt == null)
                {
                    tt = new TransportType();
                    tt.TransportTypeName = transportName;
                    context.TransportTypes.InsertOnSubmit(tt);
                    context.SubmitChanges();
                }

                DestinationTransport dt = new DestinationTransport();
                dt.TransportTypeId = tt.TransportTypeId;
                dt.DestinationId = dest.DestinationId;
                context.DestinationTransports.InsertOnSubmit(dt);
                context.SubmitChanges();
            }

            foreach(var facilityName  in facilities)
            {
                Facility f = (from t in context.Facilities
                                    where t.FacilityName == facilityName
                                    select t).SingleOrDefault();
                if (f == null)
                {
                    f = new Facility();
                    f.FacilityName = facilityName;
                    context.Facilities.InsertOnSubmit(f);
                    context.SubmitChanges();
                }

                DestinationFacility df = new DestinationFacility();
                df.FacilityId = f.FacilityId;
                df.DestinationId = dest.DestinationId;
                context.DestinationFacilities.InsertOnSubmit(df);
                context.SubmitChanges();
            }
        }

        public void DeleteDestination(int destinationId)
        {
            context.DestinationFacilities.DeleteOnSubmit((from d in context.DestinationFacilities
                                                          where d.DestinationId == destinationId
                                                          select d).SingleOrDefault());

            context.DestinationTransports.DeleteOnSubmit((from d in context.DestinationTransports
                                                          where d.DestinationId == destinationId
                                                          select d).SingleOrDefault());

            context.Destinations.DeleteOnSubmit((from d in context.Destinations
                                                 where d.DestinationId == destinationId
                                                 select d).SingleOrDefault());
            context.SubmitChanges();
        }

        public void ModifyDestination(int destinationId, Destination newDestination)
        {
            Destination toChange = (from d in context.Destinations
                                   where d.DestinationId == destinationId
                                   select d).SingleOrDefault();

            newDestination.DestinationId = toChange.DestinationId;
            toChange = newDestination;
            context.SubmitChanges();
        }

        public IQueryable<Destination> FindDestination(string destination,decimal? startPrice,decimal? endPrice,DateTime? startDate,DateTime? endDate,string refNum)
        {
            var result = (from d in context.Destinations
                         where (destination == null ||  d.DestinationName.Contains(destination))
                            && (startPrice == null || d.Price >= startPrice)
                            && (endPrice == null || d.Price <= endPrice)
                            && (startDate == null || d.StartDate >= startDate)
                            && (endDate == null || d.EndDate <= endDate)
                            && (refNum == null || refNum == d.ReferenceNumber)
                         select d);
            
            return result;
        }

        public IQueryable<Destination> GetDestination(int destinationId)
        {
            var result = (from d in context.Destinations
                          where d.DestinationId == destinationId
                          select d) ;
            return result;
        }

        public void CreateReservation(string destinationRefNum, string reservationRefNum, decimal price, string notes, params People[] people)
        {
            Reservation res = new Reservation();
            res.ReferenceNumber = reservationRefNum;
            res.TotalPrice = price;
            res.Notes = notes;
            res.DestinationId = (from d in context.Destinations
                                 where d.ReferenceNumber == destinationRefNum
                                 select d.DestinationId).SingleOrDefault();
            context.Reservations.InsertOnSubmit(res);
            context.SubmitChanges();

            foreach (People person in people)
            {
                var check = (from p in context.Peoples
                            where p.PassportNumber == person.PassportNumber
                            select p).SingleOrDefault();
                if (check == null)
                {
                    context.Peoples.InsertOnSubmit(person);
                }
                ReservationPeople rp = new ReservationPeople();
                rp.ReservationId = res.ReservationId;
                rp.PersonId = person.PersonId;
                context.ReservationPeoples.InsertOnSubmit(rp);
                context.SubmitChanges();
            }
        }

        public void DeleteReservation(int reservationId)
        {
            context.ReservationPeoples.DeleteOnSubmit((from p in context.ReservationPeoples
                                                      where p.ReservationId == reservationId
                                                      select p).SingleOrDefault());
            context.Reservations.DeleteOnSubmit((from p in context.Reservations
                                                 where p.ReservationId == reservationId
                                                 select p).SingleOrDefault());
        }

        public Reservation FindReservation(string refNum)
        {
            return (from r in context.Reservations
                   where refNum == r.ReferenceNumber
                    select r).SingleOrDefault().Clone() ;
        }

        public void AddPayment(int reservationId,PaymentEnum status)
        {
            Reservation reservation = (from r in context.Reservations
                               where reservationId == r.ReservationId
                               select r).SingleOrDefault();
            reservation.PaymentStatus = status;
            context.SubmitChanges();
       
        }

        public System.Data.Linq.Table<Destination> Destinations
        {
            get
            {
                return context.Destinations;
            }
        }

        public HolidayDataClassesDataContext DataContext
        {
            get
            {
                return context;
            }
        }

        public System.Data.Linq.Table<Facility> Facilities
        {
            get
            {
                return context.Facilities;
            }
        }

        public System.Data.Linq.Table<TransportType> TransportTypes
        {
            get
            {
                return context.TransportTypes;
            }
        }

        public System.Data.Linq.Table<Board> Boards
        {
            get
            {
                return context.Boards;
            }
        }

        public void SaveChanges()
        {
            context.SubmitChanges();
        }
         
    }
}
